﻿
Imports System.Data
Imports System.Configuration
Imports Npgsql

''' <summary>
''' 
''' </summary>
Public Class NpgSqlHelper
    ''' 
    Private myConnection As NpgsqlConnection = Nothing
    Private ReadOnly RETURNVALUE As String = "RETURNVALUE"
    'Private ReadOnly SQLCONNECTSTR As String = "server=192.168.111.110;uid=sa;pwd=adminstrator;database=MediacnvNet"
    Private ReadOnly SQLCONNECTSTR As String = "Server=localhost;Port=5432;User Id=postgres;Password=postgres;Database=feed;"

    ''' <summary>
    ''' 
    ''' </summary>
    Private Sub Open()
        ' 
        If myConnection Is Nothing Then
            'myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
            myConnection = New NpgsqlConnection(SQLCONNECTSTR)
        End If
        If myConnection.State = ConnectionState.Closed Then
            Try
                ''''''
                myConnection.Open()
            Catch ex As Exception
                SystemError.SystemLog(ex.Message)
                ''''''
            Finally
            End Try
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    Public Sub Close()
        ''''''
        If myConnection IsNot Nothing Then
            ''''''
            If myConnection.State = ConnectionState.Open Then
                myConnection.Close()
            End If
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    Public Sub Dispose()
        ' 
        If myConnection IsNot Nothing Then
            myConnection.Dispose()
            myConnection = Nothing
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <returns></returns>
    Public Function RunProc(ByVal procName As String) As Integer
        Dim cmd As NpgSqlCommand = CreateProcCommand(procName, Nothing)
        Try
            ''''''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try

        ''''''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <returns></returns>
    Public Function RunProc(ByVal procName As String, ByVal prams As NpgsqlParameter()) As Integer
        Dim cmd As NpgSqlCommand = CreateProcCommand(procName, prams)
        Try
            ''''''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try

        ''''''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="dataReader"></param>
    Public Sub RunProc(ByVal procName As String, ByRef dataReader As NpgsqlDataReader)
        ''''''
        Dim cmd As NpgsqlCommand = CreateProcCommand(procName, Nothing)

        Try
            ''''''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            ''''''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="dataReader"></param>
    Public Sub RunProc(ByVal procName As String, ByVal prams As NpgsqlParameter(), ByRef dataReader As NpgsqlDataReader)
        ''''''
        Dim cmd As NpgSqlCommand = CreateProcCommand(procName, prams)

        Try
            ''''''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            ''''''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="dataSet"></param>
    Public Sub RunProc(ByVal procName As String, ByRef dataSet As DataSet, Optional ByVal prams As NpgsqlParameter() = Nothing)
        If dataSet Is Nothing Then
            dataSet = New DataSet()
        End If
        ''''''
        Dim da As NpgSqlDataAdapter = CreateProcDataAdapter(procName, prams)

        Try
            ''''''
            da.Fill(dataSet)
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try
    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="DataTable"></param>
    Public Sub RunProc(ByVal procName As String, ByRef dataTable As DataTable, Optional ByVal prams As NpgsqlParameter() = Nothing)
        If dataTable Is Nothing Then
            dataTable = New DataTable()
        End If
        ''''''
        Dim da As NpgSqlDataAdapter = CreateProcDataAdapter(procName, prams)

        Try
            ''''''
            da.Fill(dataTable)
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <param name="DataTable"></param>
    Public Sub RunProc2(ByVal procName As String, ByRef dataTable As DataTable, Optional ByVal item As NpgSqlUtilities.STC_Item() = Nothing, Optional ByVal prams As NpgsqlParameter() = Nothing)
        If dataTable Is Nothing Then
            dataTable = New DataTable()
        End If
        If item IsNot Nothing Then
            For i As Integer = 0 To item.Length - 1
                procName += item(i).SqlKey
                'tbl.Sql += item[i].ItemId + "+'%'";
                procName += item(i).ItemId
            Next
        End If
        ''''''
        Dim da As NpgSqlDataAdapter = CreateProcDataAdapter(procName, prams)

        Try
            ''''''
            da.Fill(dataTable)
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <returns></returns>
    Public Function RunSQL(ByVal cmdText As String) As Integer
        Dim cmd As NpgSqlCommand = CreateNpgSqlCommand(cmdText, Nothing)
        Try
            ''''''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try

        ''''''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    ''' <returns></returns>
    Public Function RunSQL(ByVal cmdText As String, ByVal prams As NpgsqlParameter()) As Integer
        Dim cmd As NpgSqlCommand = CreateNpgSqlCommand(cmdText, prams)
        Try
            ''''''
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try

        ''''''
        Return CInt(cmd.Parameters(RETURNVALUE).Value)
    End Function

    ''' <summary>
    ''' SQL
    ''' </summary>
    ''' <param name="cmdText">SQL</param> 
    ''' <param name="dataReader">DataReader</param>
    Public Sub RunSQL(ByVal cmdText As String, ByRef dataReader As NpgsqlDataReader)
        ''''''
        Dim cmd As NpgSqlCommand = CreateNpgSqlCommand(cmdText, Nothing)

        Try
            ''''''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            ''''''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' SQL
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    ''' <param name="dataReader">DataReader</param>
    Public Sub RunSQL(ByVal cmdText As String, ByVal prams As NpgsqlParameter(), ByRef dataReader As NpgsqlDataReader)
        ''''''Command
        Dim cmd As NpgSqlCommand = CreateNpgSqlCommand(cmdText, prams)

        Try
            ''''''
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            dataReader = Nothing
            ''''''
            SystemError.SystemLog(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="dataSet">DataSet</param>
    Public Sub RunSQL(ByVal cmdText As String, ByRef dataSet As DataSet)
        If dataSet Is Nothing Then
            dataSet = New DataSet()
        End If
        ''''''NpgSqlDataAdapter
        Dim da As NpgSqlDataAdapter = CreateNpgSqlDataAdapter(cmdText, Nothing)

        Try
            ''''''
            da.Fill(dataSet)
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    Public Function GetSQLdataTbl(ByVal cmdText As String, ByVal prams As NpgsqlParameter()) As DataTable
        Dim dt As New DataTable
        ''''''NpgSqlDataAdapter
        Dim da As NpgsqlDataAdapter = CreateProcDataAdapter(cmdText, prams)

        Try
            ''''''
            da.Fill(dt)
            Return dt
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
            Return Nothing
        Finally
            ''''''
            Close()
        End Try
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="cmdText">SQL</param>
    ''' <param name="prams">SQL</param>
    ''' <param name="dataSet">DataSet</param>
    Public Sub RunSQL(ByVal cmdText As String, ByVal prams As NpgsqlParameter(), ByRef dataSet As DataSet)
        If dataSet Is Nothing Then
            dataSet = New DataSet()
        End If
        ''''''NpgSqlDataAdapter
        Dim da As NpgSqlDataAdapter = CreateProcDataAdapter(cmdText, prams)

        Try
            ''''''
            da.Fill(dataSet)
        Catch ex As Exception
            ''''''
            SystemError.SystemLog(ex.Message)
        Finally
            ''''''
            Close()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="procName"></param>
    ''' <param name="prams"></param>
    ''' <returns></returns>
    Private Function CreateProcCommand(ByVal procName As String, ByVal prams As NpgsqlParameter()) As NpgsqlCommand
        ''''''
        Open()

        ''''''
        Dim cmd As New NpgSqlCommand(procName, myConnection)
        cmd.CommandType = CommandType.StoredProcedure

        ''''''
        If prams IsNot Nothing Then
            For Each parameter As NpgSqlParameter In prams
                cmd.Parameters.Add(parameter)
            Next
        End If

        ''''''ReturnValue
        cmd.Parameters.Add(New NpgsqlParameter(RETURNVALUE, DbType.Int32, 4, String.Empty, ParameterDirection.ReturnValue, False, 0, _
         0, DataRowVersion.[Default], Nothing))

        ''''''NpgSqlCommand
        Return cmd
    End Function

    Private Function CreateNpgSqlCommand(ByVal cmdText As String, ByVal prams As NpgsqlParameter()) As NpgsqlCommand
        ''''''
        Open()

        ''''''
        Dim cmd As New NpgSqlCommand(cmdText, myConnection)

        ''''''
        If prams IsNot Nothing Then
            For Each parameter As NpgSqlParameter In prams
                cmd.Parameters.Add(parameter)
            Next
        End If

        ''''''
        cmd.Parameters.Add(New NpgsqlParameter(RETURNVALUE, DbType.Int32, 4, String.Empty, ParameterDirection.ReturnValue, False, 0, _
         0, DataRowVersion.[Default], Nothing))

        ''''''
        Return cmd
    End Function

    Private Function CreateProcDataAdapter(ByVal procName As String, ByVal prams As NpgsqlParameter()) As NpgsqlDataAdapter
        ''''''
        Open()

        ''''''
        Dim da As New NpgSqlDataAdapter(procName, myConnection)
        'da.SelectCommand.CommandType = CommandType.StoredProcedure; //2013/02/26 shin

        ''''''
        If prams IsNot Nothing Then
            For Each parameter As NpgSqlParameter In prams
                da.SelectCommand.Parameters.Add(parameter)
            Next
        End If

        ''''''
        da.SelectCommand.Parameters.Add(New NpgsqlParameter(RETURNVALUE, DbType.Int32, 4, String.Empty, ParameterDirection.ReturnValue, False, 0, _
         0, DataRowVersion.[Default], Nothing))

        Return da
    End Function

    Private Function CreateNpgSqlDataAdapter(ByVal cmdText As String, ByVal prams As NpgsqlParameter()) As NpgsqlDataAdapter
        Open()

        Dim da As New NpgSqlDataAdapter(cmdText, myConnection)

        If prams IsNot Nothing Then
            For Each parameter As NpgSqlParameter In prams
                da.SelectCommand.Parameters.Add(parameter)
            Next
        End If

        da.SelectCommand.Parameters.Add(New NpgsqlParameter(RETURNVALUE, DbType.Int32, 4, String.Empty, ParameterDirection.ReturnValue, False, 0, _
         0, DataRowVersion.[Default], Nothing))

        Return da
    End Function

    Public Function CreateParam(ByVal ParamName As String, ByVal DbType As NpgsqlTypes.NpgsqlDbType, ByVal Size As Int32, ByVal Direction As ParameterDirection, ByVal Value As Object) As NpgsqlParameter
        Dim param As NpgsqlParameter

        If Size > 0 Then
            param = New NpgsqlParameter(ParamName, DbType, Size)
        Else
            param = New NpgsqlParameter(ParamName, DbType)
        End If

        param.Direction = Direction
        If Not (Direction = ParameterDirection.Output AndAlso Value Is Nothing) Then
            param.Value = Value
        End If

        Return param
    End Function

    Public Function CreateInParam(ByVal ParamName As String, ByVal Value As Object, ByVal DbType As NpgsqlTypes.NpgsqlDbType, ByVal Size As Integer) As NpgsqlParameter
        Return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value)
    End Function

    Public Function CreateOutParam(ByVal ParamName As String, ByVal DbType As NpgsqlTypes.NpgsqlDbType, ByVal Size As Integer) As NpgsqlParameter
        Return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, Nothing)
    End Function

    Public Function CreateReturnParam(ByVal ParamName As String, ByVal DbType As NpgsqlTypes.NpgsqlDbType, ByVal Size As Integer) As NpgsqlParameter
        Return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, Nothing)
    End Function
End Class
